package com.api.gbhg.lbj.tool;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @author lbj
 * @version 1.0
 * @description: TODO 公共方法
 * @date 2020/12/31 15:46
 */
public class Tools {
    private static final Logger LOGGER = LoggerFactory.getLogger("gbhg");
    // 查询用户名
    public static String getUserNameById(String id){
        RecordSet rts=new RecordSet();
        String sql="select lastname from hrmResource where id="+id;
            rts.executeQuery(sql);
            rts.next();
            String lastname = rts.getString("lastname");
            return lastname;
    }
    //查询是否控制使用单位报销额度
    public static RecordSet getsfkzsydwbxed(String lcjlid){
        RecordSet rs=new RecordSet();
        String querySql="select lcid,lcjlsqjlid,xmmin,sfkzsydwbxed from uf_jingfei_version where requestId="+ lcjlid;
        //LOGGER.info("查询是否控制使用单位报销额度---:"+querySql);
        rs.executeQuery(querySql);
        return rs;
    }

    public static String getSBKSById(String id){
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();
        String sql="select departmentname from HrmDepartment where id="+id;
        rts.executeQuery(sql);
        rts.next();
        String departmentname = rts.getString("departmentname");
        return departmentname;
    }


    //部门id
    public static String getDepartmentId(String id){
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();
        String sql="select departmentid from HrmResource where id="+id;
        rts.executeQuery(sql);
        rts.next();
        String departmentid = rts.getString("departmentid");
        if (StringUtils.isBlank(departmentid)){
            departmentid="";
        }
        return departmentid;
    }
    //分部门id
    public static String getsubcompanyId(String id){
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();
        String sql="select subcompanyid1 from HrmResource where id="+id;
        rts.executeQuery(sql);
        rts.next();
        String subcompanyid1 = rts.getString("subcompanyid1");
        if (StringUtils.isBlank(subcompanyid1)){
            subcompanyid1="";
        }
        return subcompanyid1;
    }
    //经济分类id
    public static String getuf_jjzcflkmkId(String id){
        //LOGGER.info("经济分类id-------------:"+id);
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();
        String sql="select id from (select *,substring(kmc, charindex('[',kmc)+1, charindex(']',kmc)-2) kc1 from uf_jjzcflkmk) a where a.kc1="+"'"+id+"'";
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("id");
    }
    public static String getuf_jjzcflkmkKmc(String id){
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();
        String sql="select kmc from uf_jjzcflkmk where id="+"'"+id+"'";
        rts.executeQuery(sql);
        rts.next();
        String kmc = rts.getString("kmc");
        kmc=kmc.substring(kmc.indexOf("[")+1,kmc.indexOf("]"));
        return kmc;
    }
    //功能科目id
    public static String getuf_gnkmId(String id,String nd){
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();
        String sql="select id from uf_gnkm where kmdm="+"'"+id+"'"+ " and nd="+nd;
        LOGGER.info("sql:"+sql);
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("id");
    }
    //功能科目id-excel导入用
    public static String getExceluf_gnkmId(String id){
        if(id.isEmpty()) return "";
        RecordSet rts=new RecordSet();

        String sql="select id from uf_gnkm where kmdm like"+"'%"+id+"%'";
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("id");
    }

    //各项目对应功能科目id
    public static String getuf_gxmdygnkmId(String id){
        RecordSet rts=new RecordSet();
        String sql="select kmdm from uf_gxmdygnkm where id="+"'"+id+"'";
        LOGGER.info("sql:"+sql);
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("kmdm");
    }



    /**
     * 根据id查功能科目
     * @param id
     * @return
     */
    public static String getSubjectsGnkm(int id){
        RecordSet rs = new RecordSet();
        String sql = "select gnkm from uf_zyczbksr_km where id = "+id+"";
        LOGGER.info("sql:"+sql);
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("gnkm");
    }
    public static int getSubjectsGnkm2(int id){
        RecordSet rs = new RecordSet();
        String sql = "select szgnkm from uf_zyczbksr_xj where id = "+id+"";
        LOGGER.info("sql:"+sql);
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("szgnkm");
    }
    public static  RecordSet theCentral(String zcqd,int year,String sbdw){
        RecordSet rs = new RecordSet();
        String sql = "select gnkm from uf_fzysr where szfl="+zcqd+" and nf="+year+" and dw="+sbdw+"";
        LOGGER.info("sql:"+sql);
        rs.executeQuery(sql);
        return rs;
    }

    // 查询申报单位
    public static String getSBDWById(String id){
        RecordSet rts=new RecordSet();
        String sql="select SUBCOMPANYNAME from hrmsubcompany where id="+id;
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("SUBCOMPANYNAME");
    }


    // 查询用户名id
    public static String getUseridByName(String name){
        RecordSet rts=new RecordSet();
        rts.executeQuery("select id from hrmResource where lastname="+"'"+name+"'");
        rts.next();
        String id = rts.getString("id");
        if (StringUtils.isBlank(id)){
            id="";
        }
        return id;
    }

    //获取Excel数据
    public static List<Object> getExcelData(InputStream file, String sheetName, String keyWord, Integer offset){
        List<Object> objects= ExcelTools.findColumnRowId(file,sheetName,null,keyWord,offset);//C:\Excel测试\167057016000200001_海关仪器设备购置专项.xls
        return objects;
    }
    //获取Excel数据
    public static List<Object> getExcelData(String file, String sheetName, String keyWord, Integer offset){
        List<Object> objects= ExcelTools.findColumnRowId(file,sheetName,null,keyWord,offset);//C:\Excel测试\167057016000200001_海关仪器设备购置专项.xls
        return objects;
    }
    //获取当前年份
    public static String getCurrentYear(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        Date date = new Date();
        return sdf.format(date);
    }
    // 查询部门id
    public static String getbmyId(String departmentmark){
        RecordSet rts=new RecordSet();
        String sql="select id from HrmDepartment where departmentmark="+"'"+departmentmark+"'";
        rts.executeQuery(sql);
        rts.next();
        return rts.getString("id");
    }


    // 查询项目id
    public static RecordSet getxmid(String xmdm){
        RecordSet rts=new RecordSet();
        String sql="select lcjlsqjlid from uf_jingfei_version where xmdm="+"'"+xmdm+"'";
        rts.executeQuery(sql);
        LOGGER.info("根据项目代码查询经费版本表："+sql);
        return rts;
    }

    public static String getyslylx(String ysxm){
        RecordSet rts=new RecordSet();
        rts.executeQuery("select ");
        return null;
    }
}
